package yu.zhou.njupt.importdataui.core.impl;

import java.io.DataInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;

import yu.zhou.njupt.importdataui.core.Importer;
import yu.zhou.njupt.importdataui.entity.ImportResult;
import yu.zhou.njupt.importdataui.util.RegexUtil;
import au.com.bytecode.opencsv.CSVReader;

import com.google.common.base.Joiner;

/** 
 * 用于导入csv文件(文件只有一个sheet)。一个文件代表一张表。根据文件
 * 名来确定导入方式，文件名形如'EPC_MODEL(XXXX).csv'的为普通表，
 * EPC_MODEL会被做为表名，括号是可选的；文件名
 * 形如'EPC_MODEL-20150313(XXXX).csv'的是分区表，EPC_MODEL会作为表名，'20150313'会作为分区键，
 * 'P20150313'会作为新增的分区名称。
**/
public class CsvImporter extends Importer{

    public CsvImporter(Connection conn) throws Exception {
        super(conn);
    }

    public ImportResult importData(File sourceFile){
        int count = 0;
        int recordCount = 0;
        long elapsedTime = 0;
        String filename = null;
        String tableName = null;
        String sheetName = null;
        ImportResult importResult = null;
        PreparedStatement ps = null;
        CSVReader csvReader = null;
        
        try {
            Date startTime = new Date();
            DataInputStream dis = new DataInputStream(new FileInputStream(sourceFile));
            csvReader = new CSVReader(new InputStreamReader(dis, Charset.forName("GBK")));
            filename = sourceFile.getName();
            String insertStr = null;

            tableName = RegexUtil.getValidName(filename).toUpperCase();

            System.out.println("  开始向表" + tableName + "导入数据...");
            String[] csvTitle = csvReader.readNext();
            List<String> columnList = new ArrayList<String>();
            List<String> placeholderList = new ArrayList<String>();
            List<Integer> indexList = new ArrayList<Integer>();
            for (int i = 0; i < csvTitle.length; i++) {
                String titleCellText = csvTitle[i];

                if (!titleCellText.startsWith("@")) {
                    String columnName = RegexUtil.getValidName(titleCellText);
                    if (columnName != null) {
                        columnList.add(columnName);
                        placeholderList.add("?");
                        indexList.add(i);
                    }
                }
            }
                insertStr = "insert into " + tableName + " ("
                        + Joiner.on(',').join(columnList) + ") "
                        + "values (" + Joiner.on(',').join(placeholderList) + ")";

            System.out.println(insertStr);
            ps = conn.prepareStatement(insertStr);
            for (String[] csvRow = csvReader.readNext(); csvRow != null; csvRow = csvReader
                    .readNext()) {
                int paramIndex = 1;
                for(Integer i : indexList){
                    ps.setString(paramIndex++, csvRow[i]);
                }
                ps.addBatch();
                count++;
                if (count == 10000) {
                    ps.executeBatch();
                    ps.clearBatch();
                    count = 0;
                    recordCount += 10000;
                    if (recordCount % 1000000 == 0) {
                        System.out.println("已插入" + recordCount + "条记录.");
                    }
                }
            }
            ps.executeBatch();
            recordCount += count;
            doAfterImport(tableName, filename, sheetName);
            System.out.println("导入完成.记录数：" + recordCount);
            Date endTime = new Date();
            elapsedTime = endTime.getTime() - startTime.getTime();
            importResult = new ImportResult(true, recordCount, elapsedTime);
        } catch (Exception e) {
            e.printStackTrace();
            importResult = new ImportResult(false);
        } finally {
            DbUtils.closeQuietly(ps);
            try {
                csvReader.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return importResult;
    }

}
